USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[SyozokuTeamKyogiList] スクリプト日付: 2014/05/07 13:37:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SyozokuTeamKyogiList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SyozokuTeamKyogiList]
GO

CREATE PROCEDURE [dbo].[SyozokuTeamKyogiList]
	@TAIKAINO int,
	@SYOZOKUNO int
AS
SELECT 
	K.KYOGINO,
	K.KYOGIMEI,
	KS.KYOGISYANO,
	dbo.IsRelay(KS.KYOGISYANO,SM.SYUMOKUKUBUN) AS KYOGISYANO1,
	(KS.KYOGISYAKANA + '<br/>' + KS.KYOGISYAMEI) AS KYOGISYAMEI,
	dbo.Kiroku(SK.KIROKU,SM.SYUMOKUKUBUN,SM.KIROKUKUBUN,SK.KIROKUFLG) AS KIROKU,
	SM.KIROKUKUBUN,
	SK.KIROKUFLG,
	ISNULL(WT.COUNT,0) AS COUNT
FROM TBL_KYOGISYA AS KS
LEFT OUTER JOIN (SELECT TEAMNO,
				COUNT(KYOGISYANO) AS COUNT
				FROM TBL_TEAM
				WHERE TAIKAINO = @TAIKAINO
				AND TEAMNO IN (SELECT KYOGISYANO
								FROM TBL_KYOGISYA
								WHERE TAIKAINO = @TAIKAINO
									AND KYOGISYAFLG = 1)
								GROUP BY TEAMNO ) AS WT
								ON KS.KYOGISYANO = WT.TEAMNO,
	TBL_SYOZOKU AS S,
	TBL_SANKAKYOGI AS SK,
	TBL_KYOGI AS K,
	TBL_SYUMOKU_MASTER AS SM
WHERE KS.SYOZOKUNO1 = S.SYOZOKUNO
	AND KS.TAIKAINO = S.TAIKAINO
	AND KS.TAIKAINO = SK.TAIKAINO
	AND KS.KYOGISYANO = SK.KYOGISYANO
	AND SK.TAIKAINO = K.TAIKAINO
	AND SK.KYOGINO = K.KYOGINO
	AND K.SYUMOKUNO = SM.SYUMOKUNO
	AND KS.TAIKAINO = @TAIKAINO
	AND KS.KYOGISYAFLG = 1
	AND KS.SYOZOKUNO1 = @SYOZOKUNO
	ORDER BY K.KYOGINO, KS.KYOGISYANO